﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;

namespace comm
{
    public partial class datasql : Form
    {
        public datasql()
        {
            InitializeComponent();
        }


        //变量
        string connString = "server=172.16.10.34;uid=safe;pwd=123456sa;database=db_safe";
        System.Data.SqlClient.SqlConnection conn = null;
        System.Data.SqlClient.SqlCommand command = null;

        private void GetData()
        {
            command.Notification = null;//清除
            SqlDependency dependency = new SqlDependency(command);//设置通知
            dependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);//通知事件

            using (SqlDataAdapter adapter = new SqlDataAdapter(command)) //查询数据
            {
                System.Data.DataSet ds = new DataSet();
                adapter.Fill(ds, 0, 31, "tbl_Sensor");

                System.Windows.Forms.DataGridViewTextBoxColumn AlarmStatus = new DataGridViewTextBoxColumn();
                AlarmStatus.DataPropertyName = "AlarmStatus";
                AlarmStatus.HeaderText = "传感器状态";
                AlarmStatus.Name = "AlarmStatus";
                AlarmStatus.Resizable = System.Windows.Forms.DataGridViewTriState.True;



                System.Windows.Forms.DataGridViewTextBoxColumn SensorAddress = new DataGridViewTextBoxColumn();
                SensorAddress.DataPropertyName = "SensorAddress";
                SensorAddress.HeaderText = "传感器地址";
                SensorAddress.Name = "SensorAddress";
                SensorAddress.Resizable = System.Windows.Forms.DataGridViewTriState.True;


                System.Windows.Forms.DataGridViewTextBoxColumn LastTemperature = new DataGridViewTextBoxColumn();
                LastTemperature.DataPropertyName = "LastTemperature";
                LastTemperature.HeaderText = "当前温度";
                LastTemperature.Name = "LastTemperature";
                LastTemperature.Resizable = System.Windows.Forms.DataGridViewTriState.True;


                System.Windows.Forms.DataGridViewTextBoxColumn LastHumidity = new DataGridViewTextBoxColumn();
                LastHumidity.DataPropertyName = "LastHumidity";
                LastHumidity.HeaderText = "当前湿度";
                LastHumidity.Name = "LastHumidity";
                LastHumidity.Resizable = System.Windows.Forms.DataGridViewTriState.True;

                System.Windows.Forms.DataGridViewTextBoxColumn LastUpdateTime = new DataGridViewTextBoxColumn();
                LastUpdateTime.DataPropertyName = "LastUpdateTime";
                LastUpdateTime.HeaderText = "时间";
                LastUpdateTime.Name = "LastUpdateTime";
                LastUpdateTime.Resizable = System.Windows.Forms.DataGridViewTriState.True;
               



                dataGridView1.DataSource = ds.Tables["tbl_Sensor"];
                this.dataGridView1.Columns.Clear();
                //this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { AlarmStatus });
                //dataGridView1.Columns["AlarmStatus"].ReadOnly = true;
                this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { SensorAddress });
                dataGridView1.Columns["SensorAddress"].ReadOnly = true;
                this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { LastTemperature });
                dataGridView1.Columns["LastTemperature"].ReadOnly = true;
                this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { LastHumidity });
                dataGridView1.Columns["LastHumidity"].ReadOnly = true;
                this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { LastUpdateTime });
                dataGridView1.Columns["LastUpdateTime"].ReadOnly = true;

            }
           
        }

        void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            //因为是子线程，需要用invoke方法更新ui
            if (this.InvokeRequired)
            {
                this.Invoke(new OnChangeEventHandler(sqlDependency_OnChange), new object[] { sender, e });
            }
            else
            {
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= sqlDependency_OnChange;//通知之后，当前dependency失效，需要重新getdata并且设置通知
                GetData();

            }
        }

        //初始化
        private void datasql_Load(object sender, EventArgs e)
        {
            Type type = dataGridView1.GetType();
            PropertyInfo pi = type.GetProperty("DoubleBuffered",BindingFlags.Instance | BindingFlags.NonPublic);
            pi.SetValue(dataGridView1, true, null);
            //以上为防止datagridview闪烁

            conn = new System.Data.SqlClient.SqlConnection(connString);
            command = conn.CreateCommand();
            command.CommandText = "SELECT ID,SensorAddress,SensorCode,LastTemperature,LastHumidity,LastUpdateTime,AlarmStatus FROM dbo.tbl_Sensor where status=0 order by SensorAddress ";

            SqlDependency.Start(connString);//启动
            GetData();//获取数据
        }

        private void datasql_FormClosed(object sender, FormClosedEventArgs e)
        {
            //清理现场
            SqlDependency.Stop(connString);
            conn.Close();
            conn.Dispose();
        }

        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e == null || e.Value == null || !(sender is DataGridView))
            {
                return;
            }
            DataGridView dgv = (DataGridView)sender;
            object originalValue = e.Value;

            if (e.ColumnIndex == dgv.Columns["LastTemperature"].Index)
            {
                if (e.Value.ToString() == "")
                {
                    int op = e.RowIndex;
                    dgv.Rows[op].DefaultCellStyle.BackColor = Color.Yellow;
                }
            }

            if (e.ColumnIndex == dgv.Columns["LastUpdateTime"].Index)   //格式化日期
            {
                if (e.Value != null)
                {
                    System.TimeSpan nd = System.DateTime.Now - Convert.ToDateTime(e.Value);
                    if (nd.TotalMinutes > 1)
                    {
                        int op = e.RowIndex;
                        dgv.Rows[op].DefaultCellStyle.BackColor = Color.YellowGreen;
                        //e.RowIndexCellStyle.BackColor = Color.Yellow;
                    }
                    e.Value = Convert.ToDateTime(e.Value).ToString("MM月dd日-HH时mm分ss秒");
                }
            }
        }






    }




}
